<?php

class Transaction extends CI_Controller {

	var $page  = 'transactions';
	var $action  = '';


	public function index(){
		/*
	$details['username'] = "MY USERNAME";
	$details['password'] = "MY PASSWORD";
	$this->sendEmail($emailMessageTemplate , $details);
		*/

		
		$data = '';

		if(isset($_REQUEST['download'])){
			$this->generateTransactionReport();
		}


		if(isset($_REQUEST['search'])){
					$data['transactionList'] = $this->searchTransactions($_REQUEST['dr_number'], $_REQUEST['clienttracknumber'], $_REQUEST['firstname'] , $_REQUEST['lastname'] ,$_REQUEST['commodity'] , $_REQUEST['deliverypoint']);
					$total = $data['transactionList']->num_rows();
					$data['successMessage'] = "Search Results : ($total) record".(($total <=1)?" was":"s were")." found.";
		}else
		$data['transactionList'] = $this->transactionList();


		$data['container'] = $this->db->query("select * from container where valid = 1");
		//$data['container'] = $container->row();

		$this->admin($data,null);
	}

	public function add(){
		$this->action = 'add';
		$data = '';
			
		if(isset($_REQUEST['searchByClient'])){
			$firstname = $_REQUEST['firstname'];
			$lastname = $_REQUEST['lastname'];
			$middlename = $_REQUEST['middlename'];
			$clientTrackNum = $_REQUEST['clientTrackNum'];


			$data['firstname'] = $firstname;
			$data['lastname'] = $lastname;
			$data['middlename'] = $middlename;
			$data['clientTrackNum'] = $clientTrackNum;


			$queryString = "";

			if($firstname != "")
			$queryString  .=  (($queryString != "")?" or ":" ") ."firstname like '%$firstname%' " ;

			if($lastname != "")
			$queryString .=    (($queryString != "")?" or ":" ") . "lastname like '%$lastname%' ";

			if($middlename != "")
			$queryString  .=   (($queryString != "")?" or ":" ") . "middlename like '%$middlename%' ";

			if($clientTrackNum != "")
			$queryString  .=    (($queryString != "")?" or ":" ") . "referencenumber like '%$clientTrackNum%' ";



			if($queryString != "")
			$queryString= " and $queryString";

			$searchedClients =  $this->db->query("select * from users where usertype = '".CLIENT."' $queryString");

			$data['searchedClients'] = $searchedClients;

			if($searchedClients->num_rows() == 0)
			$data['errorMessage'] = "No Result Found";
			else
			$data['successMessage'] = "(".$searchedClients->num_rows().") Record".(($searchedClients->num_rows()>1)?"s were":" was")." Found";



		}
			
		$this->admin($data,null);
	}

	public function create($clientId){
		$data = '';
		$client =  $this->db->query("select * from users where usertype = '".CLIENT."' and id = $clientId  and valid = 1 limit 1");
			
		if($client != null){
			$this->action = 'addTransaction';
			$data['client'] = $client->row();
		}

		$data['container'] = $this->db->query("select * from container where valid = 1");
			
		$this->admin($data,null);
	}


	public function edit($id){
		$data['editId'] = $id;
		$this->action = 'edit';
		$query = $this->getTransaction($id);
			
		if($query->row() != null)
							$data['transactionDetails'] = $query->row();

							$data['container'] = $this->db->query("select * from container where valid = 1");
								

							$this->admin($data,null);


	}

	public function save(){

		$createdby = $this->session->userdata('user_id');

		$data = '';
			
		$clientid = $_REQUEST['clientid'];
		$commodity = $_REQUEST['commodity'];
		$volume_packages= $_REQUEST['volume_packages'];
		$volume_volume= $_REQUEST['volume_volume'];
		$volume_qty= $_REQUEST['volume_qty'];
		$volume_containersize = $_REQUEST['volume_containersize'];
		$truck_qty = $_REQUEST['truck_qty'];
		$truck_company = $_REQUEST['truck_company'];
		$truck_name = $_REQUEST['truck_name'];
		$truck_number = $_REQUEST['truck_number'];
		//$truck_qty = $_REQUEST['truck_qy'];
		$truck_type = $_REQUEST['truck_type'];
		$pickupaddress = $_REQUEST['pickupaddress'];
		$dateofpickup = $_REQUEST['dateofpickup'];
		$deliverypoint = $_REQUEST['deliverypoint'];
		$onboarddate = $_REQUEST['onboarddate'];
		$intransitlocation = $_REQUEST['intransitlocation'];
		$timeofpickup = $_REQUEST['timeofpickup'];
		$onboardtime = $_REQUEST['onboardtime'];
			

		if($this->validateFields() == FALSE){
			$data['errorMessage'] = validation_errors();
			$client =  $this->db->query("select * from users where usertype = '".CLIENT."' and id = $clientid limit 1");
			if($client != null){
				$this->action = 'addTransaction';
				$data['client'] = $client->row();
			}

			$this->admin($data,null);
			return;
		}
			
			
		$isUpdate = false;
		if(isset($_REQUEST['id']))
		$isUpdate  = true;

		if($dateofpickup != ""){
			$time1 = strtotime( $dateofpickup );
			$dateofpickup = date( 'y-m-d', $time1 );
		}

		if($onboarddate != ""){
			$time3 = strtotime( $onboarddate );
			$onboarddate = date( 'y-m-d', $time3 );
		}
			

		if($isUpdate){


			$remarks = $_REQUEST['remarks'];
			$status = $_REQUEST['status'];
			$datedelivered = $_REQUEST['datedelivered'];
			$timedelivered = $_REQUEST['timedelivered'];
			$notes = $_REQUEST['notes'];
			//$datedelivered = str_replace("/", "-", $datedelivered);


			if($datedelivered != ""){
				$time = strtotime( $datedelivered );
				$datedelivered = date( 'y-m-d', $time );
			}


			if($isUpdate)
			$this->db->query("
			UPDATE transaction SET commodity='$commodity',pickupaddress='$pickupaddress',dateofpickup='$dateofpickup',deliverypoint='$deliverypoint',onboarddate='$onboarddate',intransitlocation='$intransitlocation',datedelivered='$datedelivered',status='$status',remarks='$remarks',timeofpickup='$timeofpickup',onboardtime='$onboardtime',timedelivered='$timedelivered',updatedby='$createdby',notes='$notes',volume_qty='$volume_qty', volume_packages = '$volume_packages' , volume_volume = '$volume_volume',   volume_containersize='$volume_containersize',truck_qty='$truck_qty',truck_name='$truck_name',truck_company='$truck_company',truck_number='$truck_number',truck_type='$truck_type', createdon = NOW() , updatedon = NOW()  WHERE id =".$_REQUEST['id']);


			$this->createLog('TRANSACTION' , 'UPDATED' ,  "Updated Transaction where id = ".$_REQUEST['id']);
			$data['successMessage'] = "Transaction was successfully Updated.";
		}
		else{

			/*clientid , commodity, volume_qty, volume_containersize , truck_qty , truck_name , truck_company, truck_number, truck_type,
			 pickupaddress,dateofpickup,deliverypoint, onboarddate, intransitlocation, timeofpickup, onboardtime,
			 */
			$this->db->query("INSERT INTO transaction(id , clientid , commodity, volume_qty, volume_containersize , truck_qty , truck_name , truck_company, truck_number, truck_type, pickupaddress,dateofpickup,deliverypoint, onboarddate, intransitlocation, timeofpickup, onboardtime,createdby,updatedby , updatedon , valid , dr_number) VALUES (NULL,$clientid , '$commodity', $volume_qty, $volume_containersize , $truck_qty , '$truck_name' , '$truck_company', '$truck_number', '$truck_type', '$pickupaddress', '$dateofpickup', '$deliverypoint', '$onboarddate', '$intransitlocation', '$timeofpickup', '$onboardtime',$createdby,$createdby, NOW(), 1 ,'DR".time()."')");

			$data['successMessage'] = "New Transaction was successfully Created.";
			
			
			//FOR EMAIL MESSAGE
			$emailMessageTemplate = "addednewtransaction";
			
			//$clientid
			$k = $this->db->query("select * from users where id = ".$_REQUEST['editId']." and valid= 1 limit 1" );
			if(sizeof($k) > 0){
				
				$clientRecord = $k->row();
				
				$newTransactionId = $this->db->insert_id();
				
				$details['receipients'] = array($clientRecord->email);
				
				$query = $this->getTransaction($newTransactionId);
				
			    $details['transactionDetails'] = $query->row();
			    
			    $details['clientRecord'] = $clientRecord;
				
				$this->sendEmail($emailMessageTemplate,  $details ,   "Transaction in Skybest was Successfully Created");
			}

			$this->createLog('TRANSACTION' , 'CREATED' ,  "Created new Transaction");
		}


		$data['transactionList'] = $this->transactionList();
		$this->admin($data,null);
	}

		public function main(){

			$data['mastheadImage'] = 'masthead.jpg';
				
			$this->loadAdminPage($page,$data);
		}

		public function delete($id){

			$data['deleteId'] = $id;
				
			$query = $this->db->query("update transaction set valid = 0 where id = $id and valid= 1 limit 1 " );
				
			$this->createLog('TRANSACTION' , 'DELETED' ,  "Deleted Transaction where id = $id");
				
			$this->index();
				
		}


		public function transactionList(){
			//$query = 'SELECT CONCAT( client.firstname," ", client.lastname ) AS clientname,client.id as clientid, trans.id as trans_id, referencenumber, clientid ,notes, commodity, volume, _4wheeler,';

			$query = "SELECT trans.createdon as trans_createdon ,CONCAT( client.firstname,' ', client.lastname ) AS clientname,client.id as clientid, trans.id as trans_id, referencenumber ,clientid ,  notes ,commodity, volume_qty, volume_containersize , truck_qty , truck_name , truck_company, truck_number, truck_type,
pickupaddress,dateofpickup,deliverypoint, onboarddate, intransitlocation, timeofpickup, onboardtime,dr_number , status, datedelivered, remarks, timedelivered,onboardtime , timeofpickup from ";
			//$query .= ' _6wheeler, _10wheeler, _10container, _20container, _40container, pickupaddress, dateofpickup, deliverypoint, onboarddate, intransitlocation, dr_number,status,datedelivered,remarks,timedelivered,onboardtime,timeofpickup FROM';

			$query .= ' users AS client, transaction AS trans where client.id = trans.clientid and trans.valid =1';
			return $this->db->query($query);
		}



		public function searchTransactions($dr_number, $clienttracknumber, $firstname , $lastname ,$commodity, $deliverypoint){
			//$query = 'SELECT CONCAT( client.firstname," ", client.lastname ) AS clientname,client.id as clientid, trans.id as trans_id, referencenumber, clientid , commodity, volume, _4wheeler,';
			//$query .= ' _6wheeler, _10wheeler, _10container, _20container, _40container, pickupaddress, dateofpickup, deliverypoint, onboarddate, intransitlocation, dr_number,status,datedelivered,remarks ,timedelivered,onboardtime,timeofpickup  FROM';
			$query = "SELECT CONCAT( client.firstname,' ', client.lastname ) AS clientname,client.id as clientid, trans.id as trans_id, referencenumber ,clientid ,  notes ,commodity, volume_qty, volume_containersize , truck_qty , truck_name , truck_company, truck_number, truck_type,
pickupaddress,dateofpickup,deliverypoint, onboarddate, intransitlocation, timeofpickup, onboardtime,dr_number , status, datedelivered, remarks, timedelivered,onboardtime , timeofpickup from ";

			$query .= ' users AS client, transaction AS trans where client.id = trans.clientid and trans.valid = 1';


			//echo '................................';
			$queryString = "";

			if($firstname != "")
			$queryString  .="firstname like '%$firstname%'" ;

			if($lastname != "")
			$queryString .=  (($queryString != "")?" or ":" ") . "lastname like '%$lastname%'";

			if($dr_number != "")
			$queryString  .=  (($queryString != "")?" or ":" ") . "dr_number like '%$dr_number%'";

			if($clienttracknumber != "")
			$queryString  .=   (($queryString != "")?" or ":" ") . "referencenumber like '%$clienttracknumber%'";

			if($commodity != "")
			$queryString  .=   (($queryString != "")?" or ":" ") . "commodity like '$commodity'";

			if($deliverypoint != "")
			$queryString  .=   (($queryString != "")?" or ":" ") . "deliverypoint like '$deliverypoint'";



			if($queryString != "")
			$queryString= " and $queryString";

			//ECHO $query.$queryString;
			return $this->db->query($query.$queryString);
		}














		function generateTransactionReport(){
			//ECHO
			$usertype = $this->session->userdata('user_type');
			$userId = $this->session->userdata('user_id');


			$fromDate = null;

	if(isset($_REQUEST['fromDate'])){
		//$fromDate = $_REQUEST['fromDate'];
		if($_REQUEST['fromDate'] != ""){
			$time3 = strtotime( $_REQUEST['fromDate'] );
			$fromDate = date( 'Y-m-d', $time3 );
		}

	}

	$toDate = null;

	if(isset($_REQUEST['toDate'])){
		$toDate = $_REQUEST['toDate'];

		if($_REQUEST['toDate'] != ""){
			$time3 = strtotime( $_REQUEST['toDate'] );
			$toDate = date( 'Y-m-d', $time3 );
		}


	}

		
	//load our new PHPExcel library
	$this->load->library('excel');
	//activate worksheet number 1
	$this->excel->setActiveSheetIndex(0);

	$activeSheet = $this->excel->getActiveSheet();

	//name the worksheet
	$activeSheet->setTitle('Transaction list');
	//set cell A1 content with some text
	//$this->excel->getActiveSheet()->setCellValue('A1', 'This is just some text value');

	//change the font size
	//$activeSheet->getStyle('A1')->getFont()->setSize(10);
	//make the font become bold
	//$this->excel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
	//FOR HEADER ::
	$activeSheet->setCellValue('B1', $this->session->userdata('user_fullname'));
	$activeSheet->setCellValue('A1' , 'Prepared By');
	$activeSheet->getStyle('A1')->getFont()->setBold(true);
	$activeSheet->setCellValue('A2', 'Transaction - Report (Sky Best)');
	$activeSheet->getStyle('A2')->getFont()->setBold(true);
	//$activeSheet->mergeCells('A3:B3');
	$activeSheet->setCellValue('A3', 'WEEK');
	$activeSheet->getStyle('A3')->getFont()->setBold(true);
	//$activeSheet->mergeCells('C3:D3');
	$activeSheet->setCellValue('B3', $fromDate);
	//$activeSheet->mergeCells('E3:F3');
	$activeSheet->setCellValue('C3', $toDate);

	for($atoz=65 ; $atoz < 65 + 26 ;$atoz++)
	{
		//	echo chr($atoz)."----";
		$activeSheet->getColumnDimension(chr($atoz))->setWidth(30);
		if($atoz > 65)
		$activeSheet->getStyle(chr($atoz))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

	}


	// transactionListByCreatedBy($clientid , $startDate , $endDate)



	if($usertype != 'A'){
		$transactionReportItems = $this->transactionListByCreatedBy($userId ,$fromDate , $toDate);
	}
	else
	$transactionReportItems = $this->transactionList();
		
	$numberColumn = 65;



	$activeSheet->setCellValue('A2', 'Transaction - Report (Sky Best)');
	for($i = 65 ; $i < 91 ; $i++)
	$activeSheet->getStyle(chr($i).'6')->getFont()->setBold(true);
	$activeSheet->setCellValue('A6' , 'Created On');
	$activeSheet->setCellValue('B6' , 'DR Number');
	$activeSheet->setCellValue('C6' , 'Client Name');
	$activeSheet->setCellValue('D6' , 'Client Track Number');
	$activeSheet->setCellValue('E6' , 'Status');
	$activeSheet->setCellValue('F6' , 'Commodity');
	$activeSheet->setCellValue('G6' , 'Volume-Containerize');
	$activeSheet->setCellValue('H6' , 'Volume-Quantity');
	$activeSheet->setCellValue('I6' , 'Truck Details(Company)');
	$activeSheet->setCellValue('J6' , 'Truck Details(Name)');
	$activeSheet->setCellValue('K6' , 'Truck Details(Number)');
	$activeSheet->setCellValue('L6' , 'Truck-Quantity');
	$activeSheet->setCellValue('M6' , 'Truck Type');
	$activeSheet->setCellValue('N6' , 'Pick Up Address');
	$activeSheet->setCellValue('O6' , 'Date/Time of pickup');
	$activeSheet->setCellValue('P6' , 'Deivery Point');
	$activeSheet->setCellValue('Q6' , 'On Board Date/ED');
	$activeSheet->setCellValue('R6' , 'In Transit Location');
	$activeSheet->setCellValue('S6' , 'Date Delivered');
	$activeSheet->setCellValue('T6' , 'Notes');
	$activeSheet->setCellValue('U6' , 'Remarks');


	$rowNumber = 7;
	if($transactionReportItems->num_rows() > 0)
	forEach($transactionReportItems->result() as $transactionItem){

		$activeSheet->setCellValue('A'.$rowNumber , $transactionItem->trans_createdon);
						$activeSheet->setCellValue('B'.$rowNumber , $transactionItem->dr_number);
						$activeSheet->setCellValue('c'.$rowNumber , $transactionItem->clientname);
						$activeSheet->setCellValue('D'.$rowNumber , $transactionItem->referencenumber);
						$activeSheet->setCellValue('E'.$rowNumber , $transactionItem->status);
						$activeSheet->setCellValue('F'.$rowNumber , $transactionItem->commodity);
						$activeSheet->setCellValue('G'.$rowNumber , $transactionItem->volume_qty);
						$activeSheet->setCellValue('H'.$rowNumber , $transactionItem->volume_containersize);
						$activeSheet->setCellValue('I'.$rowNumber , $transactionItem->truck_company);
						$activeSheet->setCellValue('J'.$rowNumber ,$transactionItem->truck_name);
						$activeSheet->setCellValue('K'.$rowNumber , $transactionItem->truck_number);
						$activeSheet->setCellValue('L'.$rowNumber , $transactionItem->truck_qty);
						$activeSheet->setCellValue('M'.$rowNumber , $transactionItem->truck_type);
						$activeSheet->setCellValue('N'.$rowNumber , $transactionItem->pickupaddress);
						$activeSheet->setCellValue('O'.$rowNumber , $transactionItem->dateofpickup." ".$transactionItem->timeofpickup);
						$activeSheet->setCellValue('P'.$rowNumber , $transactionItem->deliverypoint);
						$activeSheet->setCellValue('Q'.$rowNumber , $transactionItem->onboarddate." ".$transactionItem->onboardtime);
						$activeSheet->setCellValue('R'.$rowNumber , $transactionItem->intransitlocation);
						$activeSheet->setCellValue('S'.$rowNumber , $transactionItem->datedelivered);
						$activeSheet->setCellValue('T'.$rowNumber , $transactionItem->notes);
							$activeSheet->setCellValue('U'.$rowNumber , $transactionItem->remarks);


							$rowNumber++;

			}

			//set aligment to center for that merged cell (A1 to D1)
			//$this->excel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
				
			$filename='meee.xls'; //save our workbook as this file name

			header('Content-Type: application/vnd.ms-excel'); //mime type
			header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
			header('Cache-Control: max-age=0'); //no cache

			//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension)
			//if you want to save it as .XLSX Excel 2007 format
			$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');
			//force user to download the Excel file without writing it to server's HD
			$objWriter->save('php://output');

				
		}



}


